/*
1. CHANGE THE NAMES OF SEVERAL TABLES AND THEIR COLUMNS
2. ADD SALT
3. PROMOCODES DISCOUNT SHOULD BE STORED IN PROMOCODES_TYPE TABLE
4. ADD SURNAME
5. LOGIN NOT NULL?
*/

DROP TABLE USER_GROUPS CASCADE CONSTRAINTS ;
DROP TABLE USERS CASCADE CONSTRAINTS ;
DROP TABLE LOCATIONS CASCADE CONSTRAINTS ;
DROP TABLE ORDERS CASCADE CONSTRAINTS ;
DROP TABLE PROMOCODES CASCADE CONSTRAINTS ;
DROP TABLE PROMOCODE_TYPES CASCADE CONSTRAINTS;

drop sequence USERS_SEQ ;
drop sequence GROUP_SEQ;
drop sequence PROMOCODES_SEQ;
drop sequence ORDERS_SEQ;
drop sequence LOCATIONS_SEQ;
drop sequence PROMOCODE_TYPES_SEQ;

CREATE table "USER_GROUPS" (
    "ID"             NUMBER(9,0) NOT NULL,
    "NAME"     VARCHAR2(30) NOT NULL,
    "DISCOUNT" NUMBER(2,0) NOT NULL,
    "COMMISSION"     NUMBER(2,0) NOT NULL,
    constraint  "USER_GROUPS_PK" primary key ("ID")
);

CREATE table "USERS" (
    "ID"                NUMBER(9,0) NOT NULL,
    "LOGIN"             VARCHAR2(50) NOT NULL,
    "PASSWORD"          VARCHAR2(50) NOT NULL,
	/*"SALT"          VARCHAR2(50) NOT NULL,*/
    "NAME"              VARCHAR2(70) NOT NULL,
	"SURNAME"              VARCHAR2(70) NOT NULL,
    "BIRTHDATE"       DATE,
    "PHONE"             VARCHAR2(20) ,
    "REGISTRATION_DATE" DATE NOT NULL,
    "GROUP_ID"          NUMBER(9,1) NOT NULL,
    "BLOCKED"            NUMBER(1,0) NOT NULL,
    constraint  "USERS_PK" primary key ("ID") 
    );

CREATE table "LOCATIONS" (
    "ID"          NUMBER(9,0) NOT NULL,
    "CENTROID"    VARCHAR2(20) NOT NULL,
    "COUNTRY"     VARCHAR2(20) NOT NULL,
    "CITY"        VARCHAR2(20) NOT NULL,
    "STATE"       VARCHAR2(20),
    "NAME"        VARCHAR2(20) NOT NULL,
     constraint  "LOCATIONS_PK" primary key ("ID")
);

CREATE table "ORDERS" (
    "ID"              NUMBER(9,0) NOT NULL,
    "USER_ID"         NUMBER(9,0) NOT NULL,
    "LOCATION_ID" NUMBER(9,0) NOT NULL,
    "ROOMS"           NUMBER(1,0) ,
    "ADULTS"          NUMBER(1,0),
    "CHILDREN"        NUMBER(1,0),
    "NIGHTS"          NUMBER(3,0) ,
    "STARS"           NUMBER(1,0),
    "PRICE"           NUMBER(5,0) NOT NULL,
    "DATE_OF_ORDER"   DATE NOT NULL,
    "CHECK_IN_DATE"    DATE NOT NULL,
    "CHECK_OUT_DATE"   DATE NOT NULL,
    constraint  "ORDERS" primary key ("ID")
);

CREATE table "PROMOCODES" (
    "ID"         NUMBER(9,0) NOT NULL,
    "CODE"       VARCHAR2(20) NOT NULL,
    "MANAGER_ID" NUMBER(9,0) NOT NULL,
    "PROMOCODE_TYPE_ID"    NUMBER(9,0) NOT NULL,
    "USED"      NUMBER(1,0) NOT NULL,
    constraint  "PROMOCODES_PK" primary key ("ID")
);


CREATE TABLE "PROMOCODE_TYPES"(
"ID"  NUMBER(9,0) NOT NULL,
"START_DATE" DATE NOT NULL,
"EXPIRATION_DATE"   DATE NOT NULL,
"DISCOUNT"   NUMBER(2,0) NOT NULL,
constraint "PROMOCODE_TYPES_PK" primary key ("ID")
);

ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_FK" 
FOREIGN KEY ("USER_ID")
REFERENCES "USERS" ("ID")ON DELETE CASCADE ENABLE;

ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_FK2" 
FOREIGN KEY ("LOCATION_ID")
REFERENCES "LOCATIONS" ("ID")ON DELETE CASCADE ENABLE;

ALTER TABLE "USERS" ADD CONSTRAINT "USERS_FK" 
FOREIGN KEY ("GROUP_ID")
REFERENCES "USER_GROUPS" ("ID")ON DELETE CASCADE ENABLE;

ALTER TABLE "PROMOCODES" ADD CONSTRAINT "PROMOCODES_FK" 
FOREIGN KEY ("MANAGER_ID")
REFERENCES "USERS" ("ID")ON DELETE CASCADE ENABLE;

ALTER TABLE "PROMOCODES" ADD CONSTRAINT "PROMOCODES_FK2" 
FOREIGN KEY ("PROMOCODE_TYPE_ID")
REFERENCES "PROMOCODE_TYPES" ("ID")ON DELETE CASCADE ENABLE;

CREATE SEQUENCE USERS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE GROUP_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PROMOCODES_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ORDERS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE LOCATIONS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PROMOCODE_TYPES_SEQ START WITH 1 INCREMENT BY 1;

insert into USER_GROUPS (ID, NAME, DISCOUNT, COMMISSION) Values(GROUP_SEQ.NEXTVAL,'administrator',15,10);
insert into USER_GROUPS (ID, NAME, DISCOUNT, COMMISSION) Values(GROUP_SEQ.NEXTVAL,'manager',15,10);
insert into USER_GROUPS (ID, NAME, DISCOUNT, COMMISSION) Values(GROUP_SEQ.NEXTVAL,'vip user',15,10);
insert into USER_GROUPS (ID, NAME, DISCOUNT, COMMISSION) Values(GROUP_SEQ.NEXTVAL, 'registered user',5,10);
insert into USER_GROUPS (ID, NAME, DISCOUNT, COMMISSION) Values(GROUP_SEQ.NEXTVAL, 'not registered user',0,10);



insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'admiminadmin@gmail.com','1a2b3c4d5e','Olga','Gulenko',to_date('06/06/1966','DD/MM/YYYY'),'0631112233',to_date('10/10/2010','DD/MM/YYYY'),1,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'leonard@gmail.com','manager2','Leonard','Hofstadter',to_date('07/11/1986','DD/MM/YYYY'),'0638764538',to_date('10/11/2013','DD/MM/YYYY'),2,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'sheldon@gmail.com','manager3','Sheldon','Cooper',to_date('09/10/1955','DD/MM/YYYY'),'0938764538',to_date('01/01/2013','DD/MM/YYYY'),2,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'penny@gmail.com','manager4','Penny','Penny',to_date('11/11/1992','DD/MM/YYYY'),'0688764538',to_date('02/03/2013','DD/MM/YYYY'),2,1);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'howard@gmail.com','manager5','Howard','Wolowitz',to_date('27/07/1978','DD/MM/YYYY'),'0678764538',to_date('04/05/2013','DD/MM/YYYY'),2,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'raj@gmail@gmail.com','manager6','Raj','Koothrappali',to_date('13/09/1975','DD/MM/YYYY'),'0668764538',to_date('06/07/2013','DD/MM/YYYY'),2,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'naryto@gmail.com','vip1','Naruto','Uzumaki',to_date('11/11/1981','DD/MM/YYYY'),'0638795746',to_date('08/09/2013','DD/MM/YYYY'),3,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'sasuke@gmail.com','vip2','Sasuke','Uchiha',to_date('12/10/1982','DD/MM/YYYY'),'0639853068',to_date('10/10/2013','DD/MM/YYYY'),3,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'sakura@gmail.com','vip3','Sakura','Haruno',to_date('13/11/1983','DD/MM/YYYY'),'0984780040',to_date('11/01/2013','DD/MM/YYYY'),3,1);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'kakashi@gmail.com','vip4','Kakashi','Hatake',to_date('14/07/1984','DD/MM/YYYY'),'0685038690',to_date('12/02/2013','DD/MM/YYYY'),3,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'zabuza@gmail.com','vip5','Zabuza','Momochi',to_date('15/09/1985','DD/MM/YYYY'),'0938750906',to_date('13/03/2013','DD/MM/YYYY'),3,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'ayn@gmail.com','ru1','Anatoly','Novoseltsev',to_date('16/11/1971','DD/MM/YYYY'),'0937884475',to_date('14/04/2013','DD/MM/YYYY'),4,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'lpk@gmail.com','ru2','Ludmila','Kalugina',to_date('17/10/1972','DD/MM/YYYY'),'0984486590',to_date('15/05/2013','DD/MM/YYYY'),4,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'ygs@gmail.com','ru3','Yuri','Samokhvalov',to_date('18/11/1974','DD/MM/YYYY'),'0630958745',to_date('16/06/2013','DD/MM/YYYY'),4,1);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'opr@gmail.com','ru4','Olga','Ryzhova',to_date('19/07/1975','DD/MM/YYYY'),'0668847896',to_date('17/07/2013','DD/MM/YYYY'),4,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'vn@gmail.com','ru5','Vova','Novoseltsev',to_date('20/09/1976','DD/MM/YYYY'),'0635490090',to_date('18/08/2013','DD/MM/YYYY'),4,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'ayn@gmail.com','nru1','Mikhail','Berlioz',to_date('01/11/1992','DD/MM/YYYY'),'0635948007',to_date('19/09/2013','DD/MM/YYYY'),5,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'lpk@gmail.com','nru2','Ivan','Ponyryov',to_date('02/10/1991','DD/MM/YYYY'),'0634475960',to_date('10/10/2013','DD/MM/YYYY'),5,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'ygs@gmail.com','nru3','Stephan','Likhodeyev',to_date('03/11/1990','DD/MM/YYYY'),'0987776647',to_date('21/01/2013','DD/MM/YYYY'),5,1);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'opr@gmail.com','nru4','Grigory','Rimsky',to_date('04/07/1989','DD/MM/YYYY'),'0634847789',to_date('22/02/2013','DD/MM/YYYY'),5,0);
insert into USERS (ID, LOGIN, PASSWORD/*, SALT*/, NAME, SURNAME, BIRTHDATE,PHONE, REGISTRATION_DATE,GROUP_ID,BLOCKED) Values(USERS_SEQ.NEXTVAL,'vn@gmail.com','nru5','Azazello','Azazello',to_date('05/09/1985','DD/MM/YYYY'),'0975783754',to_date('23/03/2013','DD/MM/YYYY'),5,0);

insert into PROMOCODE_TYPES (ID, START_DATE, EXPIRATION_DATE, DISCOUNT) Values(PROMOCODE_TYPES_SEQ.NEXTVAL,to_date('01/11/2013','DD/MM/YYYY'),to_date('31/12/2013','DD/MM/YYYY'),3);
insert into PROMOCODE_TYPES (ID, START_DATE, EXPIRATION_DATE, DISCOUNT) Values(PROMOCODE_TYPES_SEQ.NEXTVAL,to_date('01/09/2013','DD/MM/YYYY'),to_date('01/10/2013','DD/MM/YYYY'),5);
insert into PROMOCODE_TYPES (ID, START_DATE, EXPIRATION_DATE, DISCOUNT) Values(PROMOCODE_TYPES_SEQ.NEXTVAL,to_date('01/10/2013','DD/MM/YYYY'),to_date('01/11/2013','DD/MM/YYYY'),7);

insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'71CTMAF7RP4Y50RGCNGT',2,1,1);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'TEQD1DGZZIVTP2EVYXBR',2,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'Y89Z9MBFMTUQR3SR3KQG',2,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'K0IDUCIVT6HOTX4GOZBS',2,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'X1A98KUM01OP0NM96VCT',2,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'MZEPUJ5TJPKEB8NP2OD',3,2,1);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'RZ1L53KMP0PGVW02LM2X',3,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'GUYC1L6IG9RDLGRBFLK4',3,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'IZ7IPWN0500UJESSJ67O',3,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'SB92XHHYYQ7TYTL6U9KO',3,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'XOBAK1WOQ9MYHIH9SR6Q',4,1,1);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'068HB3KVU5DLLOSR7VES',4,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'20BW0Q7FFBMKST34BFM3',4,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'VRXGU1V9OXZOCAXDZO2U',4,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'GZS1RPLUHXWEVGPBVOH5',4,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'2G1A69ACB7AFMXJS7QKA',5,3,1);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'SV1N90ZU0M98APVOR7R5',5,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'OW6CCLLNHFTKUQUP52QU',5,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'7P2BUHK8FDAJLFLHBB5X',5,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'28XR6FS28DODVJ3R1JZE',5,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'XGL2H9H8SWVKMC9H72RC',6,2,1);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'FTPP4WC24MO7ATC6YW38',6,3,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'P9ZTCRX3G2UKRTY3HQAG',6,1,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'9W4D621G94POIWOPC8ET',6,2,0);
insert into PROMOCODES (ID, CODE, MANAGER_ID, PROMOCODE_TYPE_ID,USED) Values(PROMOCODE_SEQ.NEXTVAL,'R0WKFWC2YDXHKIKWMHWN',6,3,0);



insert into LOCATIONS (ID,CENTROID,COUNTRY,CITY,NAME) Values(LOCATIONS_SEQ.NEXTVAL,'37.80644,-122.417511','US','San Francisco','Fishermans Wharf');



insert into ORDERS (ID,USER_ID,LOCATION_ID,ROOMS,ADULTS,CHILDREN,NIGHTS,STARS,PRICE,DATE_OF_ORDER,CHECK_IN_DATE,CHECK_OUT_DATE) Values(ORDERS_SEQ.NEXTVAL,10,1,1,1,1,1,3,80,to_date('01/09/2013','DD/MM/YYYY'),to_date('01/10/2013','DD/MM/YYYY'),to_date('07/10/2013','DD/MM/YYYY'));



